package com.soft.dao;

import com.soft.entity.Classification;
import com.soft.entity.Commodity;
import com.soft.entity.Information;
import com.soft.entity.Knowledge;
import com.soft.entity.Recruit;
import com.soft.util.DBHelper;
import com.soft.util.PageBean;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class HomeDAO {
    public Map<String, Object> getCommodity(PageBean pb) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Commodity> list = new ArrayList<>();

        PreparedStatement cstmt = null;
        ResultSet crs = null;
        List<Classification> clist = new ArrayList<>();


        int totalCount = getTotalCount("commodity");
        pb.setTotalCount(totalCount);

        if (pb.getCurrentPage() <= 0) {
            pb.setCurrentPage(1);
        } else if (pb.getCurrentPage() > pb.getTotalPage()) {
            pb.setCurrentPage(pb.getTotalPage());
        }


        int currentPage = pb.getCurrentPage();
        int index = (currentPage - 1) * pb.getPageCount();
        int count = pb.getPageCount();

        try {
            String sql = "select * from commodity limit ?,?;";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setInt(1, index);
            stmt.setInt(2, count);
            rs = stmt.executeQuery();
            while (rs.next()) {
                Commodity lists = new Commodity();
                lists.setId(rs.getInt("id"));
                lists.setTitle(rs.getString("title"));
                lists.setPrice(rs.getFloat("price"));
                lists.setPhoto(rs.getString("photo"));
                list.add(lists);
            }


            String sqls = "select * from commodity_class";
            cstmt = DBHelper.getConnection().prepareStatement(sqls);
            crs = cstmt.executeQuery();
            while (crs.next()) {
                Classification lists = new Classification();
                lists.setId(crs.getInt("id"));
                lists.setTitle(crs.getString("title"));
                clist.add(lists);
            }

            Map<String, Object> map = new HashMap<>();
            map.put("PageDate", pb);
            map.put("list", list);
            map.put("clist", clist);
            return map;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            if (crs != null) {
                try {
                    crs.close();
                    crs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                    cstmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }













    public Map<String, Object> searchCommodity(PageBean pb, int cid, String title) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Commodity> list = new ArrayList<>();

        PreparedStatement cstmt = null;
        ResultSet crs = null;
        List<Classification> clist = new ArrayList<>();

        int totalCount = searchTotalCount(cid, title);
        pb.setTotalCount(totalCount);

        if (pb.getCurrentPage() <= 0) {
            pb.setCurrentPage(1);
        } else if (pb.getCurrentPage() > pb.getTotalPage()) {
            pb.setCurrentPage(pb.getTotalPage());
        }


        int currentPage = pb.getCurrentPage();
        int index = (currentPage - 1) * pb.getPageCount();
        int count = pb.getPageCount();



        try {
            if (title == null || "".equals(title.trim())) {
                String sql = "select * from commodity where class_id=? limit ?,?;";
                stmt = DBHelper.getConnection().prepareStatement(sql);
                stmt.setInt(1, cid);
                stmt.setInt(2, index);
                stmt.setInt(3, count);
            } else {
                String sql = "select * from commodity where title like concat('%',?,'%') and class_id=? limit ?,?;";
                stmt = DBHelper.getConnection().prepareStatement(sql);
                stmt.setString(1, title);
                stmt.setInt(2, cid);
                stmt.setInt(3, index);
                stmt.setInt(4, count);
            }
            rs = stmt.executeQuery();
            while (rs.next()) {
                Commodity lists = new Commodity();
                lists.setId(rs.getInt("id"));
                lists.setTitle(rs.getString("title"));
                lists.setPrice(rs.getFloat("price"));
                lists.setPhoto(rs.getString("photo"));
                list.add(lists);
            }


            String sqls = "select * from commodity_class";
            cstmt = DBHelper.getConnection().prepareStatement(sqls);
            crs = cstmt.executeQuery();
            while (crs.next()) {
                Classification lists = new Classification();
                lists.setId(crs.getInt("id"));
                lists.setTitle(crs.getString("title"));
                clist.add(lists);
            }

            String url = "search_commodity&cid=" + cid + "&title=" + title;
            Map<String, Object> map = new HashMap<>();
            map.put("PageDate", pb);
            map.put("list", list);
            map.put("clist", clist);
            map.put("cid", Integer.valueOf(cid));
            map.put("url", url);
            return map;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            if (crs != null) {
                try {
                    crs.close();
                    crs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            if (cstmt != null) {
                try {
                    cstmt.close();
                    cstmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }


    public Map<String, Object> getKnowledge(PageBean pb) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Knowledge> list = new ArrayList<>();


        int totalCount = getTotalCount("knowledge");

        pb.setTotalCount(totalCount);

        if (pb.getCurrentPage() <= 0) {
            pb.setCurrentPage(1);
        } else if (pb.getCurrentPage() > pb.getTotalPage()) {
            pb.setCurrentPage(pb.getTotalPage());
        }


        int currentPage = pb.getCurrentPage();
        int index = (currentPage - 1) * pb.getPageCount();
        int count = pb.getPageCount();
        try {
            String sql = "select * from knowledge limit ?,?;";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setInt(1, index);
            stmt.setInt(2, count);
            rs = stmt.executeQuery();
            while (rs.next()) {
                Knowledge lists = new Knowledge();
                lists.setId(rs.getInt("id"));
                lists.setTitle(rs.getString("title"));
                lists.setDate(rs.getString("date"));
                list.add(lists);
            }
            Map<String, Object> map = new HashMap<>();
            map.put("PageDate", pb);
            map.put("list", list);
            return map;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }



    public Map<String, Object> getNews(PageBean pb) throws Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Information> list = new ArrayList<>();


        int totalCount = getTotalCount("information");
        pb.setTotalCount(totalCount);

        if (pb.getCurrentPage() <= 0) {
            pb.setCurrentPage(1);
        } else if (pb.getCurrentPage() > pb.getTotalPage()) {
            pb.setCurrentPage(pb.getTotalPage());
        }


        int currentPage = pb.getCurrentPage();
        int index = (currentPage - 1) * pb.getPageCount();
        int count = pb.getPageCount();
        try {
            String sql = "select * from information order by date desc limit ?,? ;";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setInt(1, index);
            stmt.setInt(2, count);
            rs = stmt.executeQuery();
            while (rs.next()) {
                Information lists = new Information();
                lists.setId(rs.getInt("id"));
                lists.setTitle(rs.getString("title"));
                lists.setDate(rs.getString("date"));
                list.add(lists);
            }
            Map<String, Object> map = new HashMap<>();
            map.put("PageDate", pb);
            map.put("list", list);
            return map;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }







    public Information informationDetail(int id) throws SQLException, Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;

        String sql = "select * from Information where id=? ;";
        stmt = DBHelper.getConnection().prepareStatement(sql);
        stmt.setInt(1, id);
        rs = stmt.executeQuery();

        try {
            if (rs.next()) {
                Information lists = new Information();
                lists.setTitle(rs.getString("title"));
                lists.setAuthor(rs.getString("author"));
                lists.setContent(rs.getString("content"));
                lists.setDate(rs.getString("date"));
                return lists;
            }
            return null;
        }
        catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }







    public Knowledge knowledgeDetail(int id) throws SQLException, Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;

        String sql = "select * from knowledge where id=? ;";
        stmt = DBHelper.getConnection().prepareStatement(sql);
        stmt.setInt(1, id);
        rs = stmt.executeQuery();

        try {
            if (rs.next()) {
                Knowledge lists = new Knowledge();
                lists.setTitle(rs.getString("title"));
                lists.setAuthor(rs.getString("author"));
                lists.setContent(rs.getString("content"));
                lists.setDate(rs.getString("date"));
                return lists;
            }
            return null;
        }
        catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }







    public Map<String, Object> commodityDetail(int id) throws SQLException, Exception {
        PreparedStatement stmt = null;
        ResultSet rs = null;

        PreparedStatement cstmt = null;
        ResultSet crs = null;
        List<Classification> clist = new ArrayList<>();
        Map<String, Object> map = new HashMap<>();


        try {
            String sqls = "select * from commodity_class";
            cstmt = DBHelper.getConnection().prepareStatement(sqls);
            crs = cstmt.executeQuery();
            while (crs.next()) {
                Classification list = new Classification();
                list.setId(crs.getInt("id"));
                list.setTitle(crs.getString("title"));
                clist.add(list);
                map.put("clist", clist);
            }

            String sql = "select * from commodity where id=? ;";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            stmt.setInt(1, id);
            rs = stmt.executeQuery();
            while (rs.next()) {
                Commodity lists = new Commodity();
                lists.setTitle(rs.getString("title"));
                lists.setPrice(rs.getFloat("price"));
                lists.setSpecifications(rs.getString("specifications"));
                lists.setPlace(rs.getString("place"));
                lists.setAlcohol(rs.getString("alcohol"));
                lists.setPhoto(rs.getString("photo"));
                lists.setContent(rs.getString("content"));
                map.put("list", lists);
            }

            return map;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }


    public ArrayList<Information> indexNews() {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        ArrayList<Information> list = new ArrayList<>();

        try {
            String sql = "select * from information order by id desc limit 0,5 ";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                Information lists = new Information();
                lists.setId(rs.getInt("id"));
                lists.setTitle(rs.getString("title"));
                list.add(lists);
            }
            return list;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }


    public ArrayList<Knowledge> indexKnowledge() {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        ArrayList<Knowledge> list = new ArrayList<>();

        try {
            String sql = "select * from knowledge order by id desc limit 0,5 ";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                Knowledge lists = new Knowledge();
                lists.setId(rs.getInt("id"));
                lists.setTitle(rs.getString("title"));
                list.add(lists);
            }
            return list;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }


    public ArrayList<Recruit> getRecruit() {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        ArrayList<Recruit> list = new ArrayList<>();

        try {
            String sql = "select * from recruit ";
            stmt = DBHelper.getConnection().prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                Recruit lists = new Recruit();

                lists.setTitle(rs.getString("title"));
                lists.setJob_describe(rs.getString("job_describe"));
                lists.setRequirement(rs.getString("requirement"));
                list.add(lists);
            }
            return list;
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {

            if (rs != null) {
                try {
                    rs.close();
                    rs = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                    stmt = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }











    public int searchTotalCount(int cid, String title) throws Exception {
        PreparedStatement pStatement = null;
        ResultSet rSet = null;
        int count = 0;

        try {
            if (title == null || "".equals(title.trim())) {
                String sql = "select * from commodity where class_id=? ";
                pStatement = DBHelper.getConnection().prepareStatement(sql);
                pStatement.setInt(1, cid);
            } else {
                String sql = "select * from commodity where title like concat('%',?,'%') and class_id=? ";
                pStatement = DBHelper.getConnection().prepareStatement(sql);
                pStatement.setString(1, title);
                pStatement.setInt(2, cid);
            }
            rSet = pStatement.executeQuery();
            rSet.last();
            count = rSet.getRow();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (rSet != null) {
                try {
                    rSet.close();
                    rSet = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (pStatement != null) {
                try {
                    pStatement.close();
                    pStatement = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return count;
    }


    public int getTotalCount(String table) throws Exception {
        PreparedStatement pStatement = null;
        ResultSet rSet = null;
        String sql = "select count(*) from " + table;
        int count = 0;
        try {
            pStatement = DBHelper.getConnection().prepareStatement(sql);
            rSet = pStatement.executeQuery();
            rSet.next();
            count = rSet.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (rSet != null) {
                try {
                    rSet.close();
                    rSet = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }

            if (pStatement != null) {
                try {
                    pStatement.close();
                    pStatement = null;
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
        return count;
    }
}
